<?php	

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br/>';



$sql = 'CREATE DATABASE qtracker';
if (mysql_query($sql, $link)) {
    echo "Database *qtracker* created successfully\n<br/>";
} else {
    echo 'Error creating database: ' . mysql_error() . "\n<br/>";
}

mysql_select_db('qtracker') or die('Could not select database');

// Performing SQL query
$sql = 'CREATE TABLE IF NOT EXISTS `Announces` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `INFO_HASH` char(20) collate utf8_unicode_ci NOT NULL,
  `PEER_ID` char(20) collate utf8_unicode_ci NOT NULL,
  `PORT` int(10) unsigned NOT NULL,
  `uploaded` bigint(20) NOT NULL,
  `downloaded` bigint(20) NOT NULL,
  `left` bigint(20) NOT NULL,
  `compact` tinyint(1) default \'1\',
  `event` varchar(10) collate utf8_unicode_ci default NULL,
  `ip` int(10) unsigned default NULL,
  `numwant` int(10) unsigned default NULL,
  `key` varchar(100) collate utf8_unicode_ci default NULL,
  `passkey` char(20) collate utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `INFO_HASH` (`INFO_HASH`),
  KEY `passkey` (`passkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE TABLE IF NOT EXISTS `Peer` (
  `PEER_ID` char(20) collate utf8_unicode_ci NOT NULL,
  `IP` int(10) unsigned NOT NULL,
  `Port` int(11) unsigned NOT NULL,
  `uploaded` bigint(20) NOT NULL default \'0\',
  `downloaded` bigint(20) NOT NULL default \'0\',
  `left` bigint(20) NOT NULL default \'0\',
  `key` varchar(100) collate utf8_unicode_ci default NULL,
  `last_announce` datetime NOT NULL,
  PRIMARY KEY  (`PEER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');
$sql = 'CREATE TABLE IF NOT EXISTS `Peer_User` (
  `PEER_ID` varchar(20) collate utf8_unicode_ci NOT NULL,
  `username` varchar(20) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`PEER_ID`,`username`),
  KEY `FK_USERNAME` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE TABLE IF NOT EXISTS `Torrent` (
  `INFO_HASH` char(20) collate utf8_unicode_ci NOT NULL,
  `Name` varchar(100) collate utf8_unicode_ci NOT NULL,
  `description` varchar(500) collate utf8_unicode_ci default NULL,
  `Size` bigint(20) NOT NULL,
  `Added` datetime NOT NULL,
  `UploadedBy` varchar(20) collate utf8_unicode_ci NOT NULL,
  `metainfo` mediumblob NOT NULL,
  PRIMARY KEY  (`INFO_HASH`),
  UNIQUE KEY `Name` (`Name`),
  KEY `FK_UPLOADED_BY` (`UploadedBy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE TABLE IF NOT EXISTS `Torrent_Peer` (
  `INFO_HASH` char(20) collate utf8_unicode_ci NOT NULL,
  `PEER_ID` char(20) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`INFO_HASH`,`PEER_ID`),
  KEY `PEER_ID` (`PEER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE TABLE IF NOT EXISTS `User` (
  `username` varchar(20) collate utf8_unicode_ci NOT NULL,
  `password` varchar(20) collate utf8_unicode_ci NOT NULL,
  `passkey` char(20) collate utf8_unicode_ci NOT NULL,
  `email` varchar(100) collate utf8_unicode_ci NOT NULL,
  `downloaded` bigint(20) NOT NULL default \'0\',
  `uploaded` bigint(20) NOT NULL default \'0\',
  `joined` datetime NOT NULL,
  `name` varchar(100) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`username`),
  UNIQUE KEY `passkey` (`passkey`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'ALTER TABLE `Announces`
  ADD CONSTRAINT `Announces_ibfk_10` FOREIGN KEY (`INFO_HASH`) REFERENCES `Torrent` (`INFO_HASH`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Announces_ibfk_9` FOREIGN KEY (`passkey`) REFERENCES `User` (`passkey`) ON DELETE NO ACTION ON UPDATE CASCADE';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'ALTER TABLE `Peer_User`
  ADD CONSTRAINT `Peer_User_ibfk_3` FOREIGN KEY (`PEER_ID`) REFERENCES `Peer` (`PEER_ID`) ON DELETE CASCADE,
  ADD CONSTRAINT `Peer_User_ibfk_4` FOREIGN KEY (`username`) REFERENCES `User` (`username`) ON DELETE CASCADE';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');


$sql = 'ALTER TABLE `Torrent`
  ADD CONSTRAINT `FK_UPLOADED_BY` FOREIGN KEY (`UploadedBy`) REFERENCES `User` (`username`) ON DELETE CASCADE';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'ALTER TABLE `Torrent_Peer`
  ADD CONSTRAINT `Torrent_Peer_ibfk_2` FOREIGN KEY (`INFO_HASH`) REFERENCES `Torrent` (`INFO_HASH`) ON DELETE CASCADE,
  ADD CONSTRAINT `Torrent_Peer_ibfk_3` FOREIGN KEY (`PEER_ID`) REFERENCES `Peer` (`PEER_ID`) ON DELETE CASCADE';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');


//procedure
$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spAnnounce`(IN in_infohash CHAR(20), IN in_peerID CHAR(20), IN in_port INT, IN in_uploaded BIGINT, IN in_downloaded BIGINT, IN in_left BIGINT, IN in_compact BOOL, IN in_event VARCHAR(10), IN in_numwant INT, IN in_ip INT, IN in_key VARCHAR(100), IN in_passkey CHAR(20))
BEGIN
	INSERT INTO Announces (INFO_HASH, PEER_ID, PORT, uploaded, downloaded, Announces.left, compact, event, ip, numwant, Announces.key, passkey, time) VALUES (in_infohash, in_peerID, in_port, in_uploaded, in_downloaded, in_left, in_compact, in_event, in_ip, in_numwant, in_key, in_passkey, NOW());
	UPDATE Peer SET uploaded = in_uploaded, downloaded = in_downloaded, Peer.left = in_left, Peer.last_announce = NOW() WHERE PEER_ID = in_peerID;
	UPDATE User SET uploaded = User.uploaded + in_uploaded, downloaded = User.downloaded + in_downloaded WHERE User.passkey = in_passkey;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spCountPeers`(IN in_infohash CHAR(20), OUT seeders INT, OUT leechers INT)
BEGIN
	SELECT COUNT(*)  FROM Peer WHERE Peer.PEER_ID IN (SELECT PEER_ID FROM Torrent_Peer WHERE Torrent_Peer.INFO_HASH = in_infohash) AND Peer.left = 0 INTO seeders;
	SELECT COUNT(*)  FROM Peer WHERE Peer.PEER_ID IN (SELECT PEER_ID FROM Torrent_Peer WHERE Torrent_Peer.INFO_HASH = in_infohash) AND Peer.left != 0 INTO leechers;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spCountUsersOnline`(OUT ret INT)
BEGIN
	SELECT COUNT(*) FROM Peer_User INTO ret;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spDeleteOldPeers`()
BEGIN
	DELETE FROM Peer WHERE NOW() - INTERVAL 20 MINUTE > Peer.last_announce;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetUsernameForPasskey`(IN passkey VARCHAR(100), OUT username VARCHAR(20))
BEGIN
	SELECT username FROM User WHERE User.passkey = passkey INTO username;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spPasskeyExists`(IN passkey VARCHAR(100), OUT retval BOOL)
BEGIN
	SELECT EXISTS (SELECT * FROM User WHERE User.passkey = passkey) INTO retval;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spPeerExists`(IN peerID CHAR(20), OUT retval BOOL)
BEGIN SELECT EXISTS (SELECT * FROM Peer WHERE PEER_ID = peerID) INTO retval; END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spRegisterPeer`(IN inpeerID CHAR(20), IN infohash CHAR(20), IN inip INT(10), IN inport INT(11), IN inkey VARCHAR(100), IN inpasskey VARCHAR(100))
BEGIN
	INSERT INTO Peer (PEER_ID, IP, Port, Peer.key, last_announce) VALUES (inpeerID, inip, inport, inkey, NOW());
	INSERT INTO Peer_User (PEER_ID, Peer_User.username) VALUES (inpeerID, (SELECT User.username FROM User WHERE User.passkey = inpasskey));
	INSERT INTO Torrent_Peer(INFO_HASH, PEER_ID) VALUES (infohash, inpeerID);
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spRegisterUser`(IN in_username VARCHAR(20), IN in_password VARCHAR(20), IN in_passkey CHAR(20), IN in_email VARCHAR(100), IN in_name VARCHAR(100))
BEGIN
	INSERT INTO User (username, password, passkey, email, joined, name) VALUES(in_username, in_password, in_passkey, in_email, now(), in_name);
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spTorrentExists`(IN infohash CHAR(20), OUT value BOOL)
BEGIN
	SELECT EXISTS (SELECT * FROM Torrent WHERE INFO_HASH = infohash) INTO value;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'CREATE DEFINER=`root`@`localhost` PROCEDURE `spUserExists`(IN in_username VARCHAR(20), IN in_email VARCHAR(100), OUT retval1 BOOL, OUT retval2 BOOL)
BEGIN
	SELECT EXISTS (SELECT * FROM User WHERE User.username = in_username) INTO retval1;
	SELECT EXISTS (SELECT * FROM User WHERE User.email = in_email) INTO retval2;
END';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

$sql = 'INSERT INTO `qtracker`.`user` (`username` ,`password` ,`passkey` ,`email` ,`downloaded` ,`uploaded` ,`joined` ,`name`)
VALUES (
"default", "default", "01234567890123456789", "default@localhost", "0", "0", "2007-09-16 12:38:13", "Default"
);';
$result = mysql_query($sql) or die('Query failed: ' . mysql_error() . '<br/>');

if(mysql_errno($link))
echo('neuspesna instalacija');
else
echo('Instalacija je uspela.<br>Za inicijalno logovanje koristiti user:"default" pass:"default". <br>Napravite svoj account preko programa QTracker. <br /><br /><a href="index.php">Nazad na login</a>');

mysql_close($link);
?>
